<?php
/**
 * @desc access for MySQLi Database
 * @author HoangNV2
 */
class SysMySqli {
	public $config = array();
	public $conn = NULL;
	public $sql = '';
	public $recordset;
	public $result;

	/**
	 *using to connect to database
	 *@param $config
	 */
	function connect () {
		$host = $this->config['host'];
		$user = $this->config['username'];
		$pass = $this->config['password'];
		$dbname = $this->config['dbname'];

		
		if (!class_exists('mysqli')) $this->exceptionError('Do not support database mysqli');
		
		$this->conn = new mysqli($host,$user,$pass,$dbname) OR $this>exceptionError(mysqli_error($this->conn));
		
	}

	/**
	 * using to query a sql statement
	 */
	function query($sql=''){
		$this->sql = $sql;
		if ($this->conn == NULL) $this->connect();
		$this->recordset = @mysqli_query($this->conn,$sql) OR $this->exceptionError(mysqli_error($this->conn));
	}
	

	/**
	 * @desc use INSERT,DELETE,UPDATE
	 * @return:
	 * 		- ROLLBACK if false
	 * 		- COMMIT if true
	 */
	public function queryRollback ($sql='') {
		$this->sql = $sql;
		if ($this->conn == NULL) $this->connect();
		
		@mysqli_query("BEGIN WORK");
		$res = @mysqli_query($sql) OR $this->exceptionError(mysqli_error(), 'ROLLBACK');
		@mysqli_query("COMMIT");
		$this->recordset = $res;
		
		return $this->recordset;
	}
	/**
	 * @desc get one first of rows
	 * @return array record
	 */
	public function selectOne ($sql) {
		$sql.= "\n LIMIT 1";
		$this->query($sql);
		$this->result = mysqli_fetch_assoc($this->recordset);		
		return $this->result;
	}
	
	/**
	 * @param string $sql SQL QUERY
	 * @param string $key key of return result
	 * @param string $page_record number row of page
	 * @param string $page_no page number
	 * @return rows: array(
	 * 		key=>array(row1, row2,...),
	 * 		key=>array(row1, row2,...),
	 * 		key=>array(row1, row2,...),
	 * 		key=>array(row1, row2,...),
	 * 	)
	 */
	public function select ($sql='',$key='',$page_record=NULL,$page_no=NULL) {
		if ($key == '') {
			$this->query($sql);
			while ($row = mysqli_fetch_array($this->recordset)) {
			$this->result[] = $row;
		}	
		} else {
			$this->query($sql);
			$this->result = array();
			while ($row = mysqli_fetch_assoc($this->recordset)) {
				if (!isset($row[$key])) $this->exceptionError('Do not exist key ['.$key.']');
				
				$this->result[$row[$key]][] = $row;
			}
		}
		return $this->result;
	}


	/**
	 * @return nums row
	 */
	public function getNumRows () {
		if ($this->recordset == '') return 0;
		return mysqli_num_rows($this->recordset);
		
	}
	/**
	 * @param string $sql SQL QUERY
	 * @return rows: array(0=>array1(0=>val1,1=>val2,...), 1=>array2(0=>val1,1=>val2,...),...)
	 */
	public function getFetch ($res=NULL) {
		if (!$res) return ;
		$this->result = array();
		while ($row = mysqli_fetch_row($res)) {
			$this->result[] = $row;
		}
		return $this->result;
	}
	/**
	 * @desc insert one record
	 * @return new record id OR false
	 */
	public function insert($sql='', $keyname='') {
		$this->query($sql);
		if ($this->recordset) return true;

		return false;
	}
	/**
	 * delete one record
	 */
	
	public function delete ($sql, $limit='') {
		$this->query($sql);
		if ($this->recordset) return true;
		return false;
	}
	

	/**
	 * update
	 */
	public function update($sql) {
		$this->query($sql);
		if ($this->recordset) return true;

		return false;
	}
	/**
	 * @desc get one first of rows
	 * @return array record
	 */
	public function getSelectOne ($res=NULL) {
		if (!$res) return ;

		$this->result = mysqli_fetch_assoc($res);
		return $this->result;
	}
	/**
	 * @param string $sql SQL QUERY
	 * @return rows: array(0=>array1(0=>val1,1=>val2,...), 1=>array2(0=>val1,1=>val2,...),...)
	 */
	public function fetch ($sql) {
		$this->query($sql);
		$this->result = array();
		while ($row = mysqli_fetch_row($this->recordset)) {
			$this->result[] = $row;
		}
		return $this->result;
	}
	/**
	 * @param string $sql SQL QUERY
	 * @return rows: array(0=>array1(col1,col2,...), 1=>array2(col1, col2,...),...)
	 */
	public function fetchAll ($sql) {
		$this->query($sql);
		$this->result = mysqli_fetch_all($this->recordset);
		
		return $this->result;
	}
	/**
	 * @param string $sql SQL QUERY
	 * @return rows: array(0=>array1(col1,col2,...), 1=>array2(col1, col2,...),...)
	 */
	public function getFetchAll ($res=NULL) {
		if (!$res) return ;

		while ($row = mysqli_fetch_assoc($res)){
			$this->result[] = $row;
		}
		return $this->result;
	}
	
	/**
	 * @return print error
	 */
	public function exceptionError($msg, $act='') {
		if ($act == 'ROLLBACK') {
			@mysqli_query("ROLLBACK");
		}
		throw new SysExceptionModel($msg);
	}

	/**
	 * @desc close connection
	 */
	public function __destruct () {
		@mysqli_close($this->conn); // Close this connection
	}
}